﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Claro.QuestionModel
{



    public class clsQuestao
    {
        
        public int codQuestao { get; set; }
        public string textoQuestao { get; set; }
        public clsAssunto assunto { get; set; }
        public string tipoQuestao { get; set; }
        public List<clsAlternativa> Alternativas { get; set; }
        public int tempoDisponivel { get; set; }
        
        public clsQuestao()
        {
            this.assunto = new clsAssunto();
            
        }

        public void SelecionarAlternativasDaQuestao()
        {
            this.Alternativas = new List<clsAlternativa>();
            this.Alternativas = clsAlternativa.SelecionarAlternativas(this.codQuestao);
        }

        public void LancarQuestao(int codEvento)
        { 
            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = "Insert QuestaoEvento (codEvento, codQuestao, codStatus, Tempo) Values (@codEvento, @codQuestao, @codstatus, @tempo)";
            cmd.Parameters.Add("@codEvento", SqlDbType.Int).Value = codEvento;
            cmd.Parameters.Add("@codQuestao", SqlDbType.Int).Value = this.codQuestao;
            cmd.Parameters.Add("@codStatus", SqlDbType.Char, 1).Value = Status.Aberto;
            cmd.Parameters.Add("@Tempo", SqlDbType.DateTime).Value = DateTime.Now;

            cmd.ExecuteNonQuery();

            cn.Close();
            cn.Dispose();
        }

        public static List<clsQuestao> SelecionarQuestoes(int codEvento)
        {
            string sql = "pr_SelecionarQuestoesDisponiveis";
            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sql;
            cmd.Parameters.Add("@codEvento", SqlDbType.Int).Value = codEvento;
            SqlDataReader dr = cmd.ExecuteReader();
            List<clsQuestao> Questoes = new List<clsQuestao>();
            while (dr.Read())
            {
                clsQuestao Q = new clsQuestao();
                Q.codQuestao = dr.GetInt32(dr.GetOrdinal("codQuestao"));
                Q.textoQuestao = dr.GetString(dr.GetOrdinal("textoQuestao"));
                Q.assunto.Descricao = dr.GetString(dr.GetOrdinal("Assunto"));
                Q.tipoQuestao = dr.GetString(dr.GetOrdinal("codTipoQuestao"));
                Q.tempoDisponivel = dr.GetInt16(dr.GetOrdinal("tempoDisponivel"));
                Q.SelecionarAlternativasDaQuestao();
                Questoes.Add(Q);
            }

            cn.Close();
            cn.Dispose();
            return Questoes;
        }

        /// <summary>
        /// Preenche a questão, inclusive com a alternativa correta
        /// </summary>
        /// <param name="codQuestao"></param>
        /// <returns></returns>
        public void SelecionarDadosQuestao()
        {
            string sql = "select textoQuestao, codTipoQuestao, tempoDisponivel" +
                        " from questao q " +
                        " where codQuestao = @codQuestao";

            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@codQuestao", SqlDbType.Int).Value = this.codQuestao;
            SqlDataReader dr = cmd.ExecuteReader();
            clsQuestao novaQuestao = new clsQuestao();
            while (dr.Read())
            {
                this.textoQuestao = dr.GetString(dr.GetOrdinal("textoQuestao"));
                this.tipoQuestao = dr.GetString(dr.GetOrdinal("codTipoQuestao"));
                this.tempoDisponivel = dr.GetInt16(dr.GetOrdinal("tempoDisponivel"));
                this.SelecionarAlternativasDaQuestao();
            }

            cn.Close();
            cn.Dispose();
        }

        public static clsQuestao SelecionarQuestaoAberta(int codEvento)
        {
            string sql = "select q.codQuestao, textoQuestao, codTipoQuestao, tempoDisponivel" +
                        " from questao q inner join questaoevento qe" +
                        " on q.codquestao = qe.codquestao" +
                        " where qe.codevento = @codevento" +
                        " and codstatus = 'A'" ;

            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@codEvento", SqlDbType.Int).Value = codEvento;
            SqlDataReader dr = cmd.ExecuteReader();
            clsQuestao novaQuestao = new clsQuestao();
            while (dr.Read())
            {
                novaQuestao.codQuestao = dr.GetInt32(dr.GetOrdinal("codQuestao"));
                novaQuestao.textoQuestao = dr.GetString(dr.GetOrdinal("textoQuestao"));
                novaQuestao.tipoQuestao = dr.GetString(dr.GetOrdinal("codTipoQuestao"));
                novaQuestao.tempoDisponivel = dr.GetInt16(dr.GetOrdinal("tempoDisponivel"));
                novaQuestao.SelecionarAlternativasDaQuestao();


                foreach (clsAlternativa alternativa in novaQuestao.Alternativas)
                    alternativa.estaCorreta = false;


                cn.Close();
                cn.Dispose();
                return novaQuestao;
            }

            cn.Close();
            cn.Dispose();
            return null;
        }

        public void Responder(int codGrupo)
        {
            SqlConnection cn = clsConexao.Conectar();

            foreach (clsAlternativa alternativa in this.Alternativas)
            {
                if (alternativa.estaCorreta)
                {
                    SqlCommand cmd = cn.CreateCommand();
                    cmd.CommandText = "insert questaogrupo" +
                                      " (codQuestao, codAlternativa, codGrupo, Tempo, textoResp)" +
                                      " values (@codQuestao, @codAlternativa, @codGrupo, @Tempo, @textoResp)";
                    cmd.Parameters.Add("@codQuestao", SqlDbType.Int).Value = this.codQuestao;
                    cmd.Parameters.Add("@codAlternativa", SqlDbType.Int).Value = alternativa.codAlternativa;
                    cmd.Parameters.Add("@codGrupo", SqlDbType.Int).Value = codGrupo;
                    cmd.Parameters.Add("@Tempo", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@TextoResp", SqlDbType.VarChar, 50).Value = "";

                    cmd.ExecuteNonQuery();
                }
            }
            
            cn.Close();
            cn.Dispose();
        }

        public void Corrigir(int codEvento)
        {
            string sql = "insert placar" +
                            " (codGrupo, codQuestao)" +
                            " select qg.codGrupo, qg.codQuestao" +
                            " from questaogrupo qg inner join alternativa A" +
                            " on (	qg.codQuestao = a.codquestao" +
                                " and qg.codalternativa = a.codalternativa	)" +
                            " inner join grupo g" +
                            " on (qg.codgrupo = g.codgrupo)" +
                            " where a.correta = 1" +
                            " and codevento = @codEvento" +
                            " and qg.codQuestao = @codQuestao";

            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@codEvento", SqlDbType.Int).Value = codEvento;
            cmd.Parameters.Add("@codQuestao", SqlDbType.Int).Value = this.codQuestao;

            cmd.ExecuteNonQuery();

            sql = "UPDATE QuestaoEvento" +
                    " SET codstatus = 'F'" +
                    " WHERE codEvento = @codEvento" +
                    " AND	codQuestao = @codQuestao";
            cmd.Parameters.Clear();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@codEvento", SqlDbType.Int).Value = codEvento;
            cmd.Parameters.Add("@codQuestao", SqlDbType.Int).Value = this.codQuestao;
            cmd.ExecuteNonQuery();


            cn.Close();
            cn.Dispose();
        }

        public string SelecionarStatusQuestao(int codEvento)
        {
            string sql = "SELECT codStatus" + 
                    " FROM QuestaoEvento" +
                    " WHERE codEvento = @codEvento" + 
                    " AND codQuestao = @codQuestao";
            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@codEvento", SqlDbType.Int).Value = codEvento;
            cmd.Parameters.Add("@codQuestao", SqlDbType.Int).Value = this.codQuestao;
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            string status = dr.GetString(dr.GetOrdinal("codStatus"));

            cn.Close();
            cn.Dispose();

            return status;
        }
    }


}
